package com.cxhome.cxinfox.customer;

import java.util.List;

import org.apache.log4j.Logger;

import com.cxhome.cxinfox.order.Order;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.traffic.managent.base.BaseService;
import com.traffic.managent.model.account.Account;
import com.traffic.managent.model.customer.Customer;
import com.traffic.managent.model.product.Product;
import com.traffic.managent.model.system.UserProsDiscountInfo;
import com.traffic.managent.util.StringUtils;

/**
 * 客户业务处理类。
 * 
 */
public class CustomerService extends BaseService {

	private final static Logger log = Logger.getLogger(CustomerService.class);
	public static final CustomerService service = new CustomerService();

	
	/**
	 * 账单 交易报表
	 * 查询客户 当月每天的充值记录，不含赠送
	 */
	
	public List<Record> queryTransactionReports(String customerId) {
		StringBuilder sql = new StringBuilder("SELECT a.d as name,b.value from  "
				+ " (SELECT ADDDATE(y.FIRST, x.ids - 1) AS d   FROM   cx_days_31 x,   (  "
				+ " SELECT   CURDATE() - INTERVAL DAY (CURDATE()) - 1 DAY AS FIRST,  "
				+ " DAY (LAST_DAY(CURDATE())) AS last   ) y   WHERE   x.ids <= y.last ) a LEFT JOIN "
				+ " (SELECT SUM(car.amount) as value,DATE_FORMAT(car.createTime,'%y-%m-%d') createTime from cx_account_record car "
				+ " where 1=1 AND DATE_FORMAT(car.createTime,'%y-%m') = DATE_FORMAT(CURDATE(),'%y-%m') AND car.customerId = ? "
				+ " AND car.status  = '0' AND car.type = '0' AND car.amount is NOT NULL GROUP BY DATE_FORMAT(car.createTime,'%y-%m-%d') ) b "
				+ " ON a.d = b.createTime ");

		return Db.find(sql.toString(), customerId);

	}
	/**
	 * 账单 充值记录
	 */
	
	public List<Account> queryTransactionRecord(String customerId, int page, int pageNumber) {

		StringBuilder sql = new StringBuilder("select tempA.*   from (  "
				+ " SELECT distinct a.createtime,a.amount AS amount1, 0.0 AS amount2,a.createName,a.currentAmount,a.currentGive, b.customerName, a.type,a.remark "
				+ " FROM cx_account_record a, cx_customer_info b , cx_user u   "
				+ " WHERE a.customerId = b.customerId AND a.status = '0' AND a.type = '0' AND a.amount IS NOT NULL and u.customerId = b.customerId "
				+ "  AND b.customerId = ? " + " union all   "
				+ " SELECT distinct a.createtime, 0.0 AS amount1, a.amount AS amount2,a.createName,a.currentAmount,a.currentGive, b.customerName, a.type,a.remark "
				+ " FROM cx_account_record a, cx_customer_info b , cx_user u    "
				+ " WHERE a.customerId = b.customerId AND a.status = '0' AND a.type = '1' AND a.amount IS NOT NULL and u.customerId = b.customerId "
				+ " AND b.customerId = ? " + ") tempA ");

		sql.append(" limit " + page + ", " + pageNumber);
		return Account.dao.find(sql.toString(), customerId, customerId);

	}
	
	/**
	 * 账单
	 * 统计客户订单详情每天
	 * @param customerId 客户id
	 * @param statisticsDate 统计日期 ， 统计昨天 则传参 2017-08-10，统计前月则 2017-07
	 * @param statisticsType 统计类型 ，0年1月2日
	 * @return
	 */
	public List<Statistics> statisticsOrderDetailDay(String customerId, String statisticsDate, String statisticsType) {
		StringBuilder sb = new StringBuilder("select * from cx_statistics cs where 1=1 ");
		if (statisticsType != null && !"".equals(statisticsType)) {
			sb.append(" and cx.customerId = '" + customerId + "'");
		}
		if (customerId != null && !"".equals(customerId)) {
			sb.append(" and cx.customerId = '" + customerId + "'");
		}
		if (statisticsDate != null && !"".equals(statisticsDate)) {
			sb.append(" and cx.statisticsDate = '" + statisticsDate + "'");
		}
		return Statistics.dao.find(sb.toString());
	}
	
	/**
	 * 订单
	 * 查询订购列表
	 */
	public List<Order> queryOrderList(String customerId, String departmentId, String start, String end, String ids,
			String cstmOrderNo, String phoneNo, String status, int page, int pageNumber) {

		StringBuilder sql = new StringBuilder(
				"select o.*,c.customerId,c.customerName,o.cstmOrderNo FROM cx_order o LEFT JOIN cx_customer_info c  ON o.customerId = c.customerId WHERE 1=1 and o.nowRoute = '0' ");

		sql.append(" AND c.customerId = '" + customerId + "'");
		if (start != null && !"".equals(start)) {
			sql.append(" and o.orderTime >= '" + start + "'");
		}
		if (end != null && !"".equals(end)) {
			sql.append(" and o.orderTime <= '" + end + "'");
		}
		if (StringUtils.isNotEmpty(phoneNo)) {
			sql.append("AND  o.phoneNo like '%" + phoneNo + "%'");
		}
		if (cstmOrderNo != null && !"".equals(cstmOrderNo)) {
			sql.append(" AND c.cstmOrderNo = '" + cstmOrderNo + "'");
		}
		if (status != null && !"".equals(status)) {
			sql.append(" AND o.status = '" + status + "'");
		}
		sql.append(" limit " + page + "," + pageNumber);
		return Order.dao.find(sql.toString());
	}
	
	
	/**
	 * 产品 
	 * 查询客户在平台的折扣产品
	 */
	public List<UserProsDiscountInfo> queryDiscountProduct(String customerId) {

		return UserProsDiscountInfo.dao.find(
				"select * from cx_user_pros_discount_info where 1=1 and del_flag = '0' and customerId = ? ",
				customerId);

	}
	
	/**
	 * 产品
	 * 1,查询本月最近订单销量最高的10个产品 ，统计产品名称，销售数量 ，销售金额，运营商
	 * @return
	 */
	public List<Record> getHighProd() {

		String slq = "SELECT c.vasProdName as name,SUM(c.orderPrice) as value, COUNT(1) as orderCount, c.operator from"
				+ " cx_order c WHERE 1=1 AND c.`status` = '7' AND DATE_FORMAT(c.orderTime, '%Y%m' ) = DATE_FORMAT(CURDATE() , '%Y%m')  GROUP BY c.vasProdId  ORDER BY COUNT(1) desc limit 10";
		return Db.find(slq);
	}
	/**
	 * 首页
	 * 查询客户当前余额与信用额度
	 */
	
	public Customer queryAmountByCustomerId(String customerId) {
		return Customer.dao.findById(customerId);

	}
	/**
	 * 首页
	 * 查询客户当前月每天的成功订单数
	 */
	public Record queryMonthDayOrders(String customerId) {
		StringBuilder sql = new StringBuilder(
				"SELECT GROUP_CONCAT(IFNULL(b.customerOrders,0) ORDER BY a.d asc) AS nowMonthOrders from ( "
						+ " SELECT   ADDDATE(y.FIRST, x.ids - 1) AS d   FROM   cx_days_31 x,   ( "
						+ " SELECT   CURDATE() - INTERVAL DAY (CURDATE()) - 1 DAY AS FIRST, "
						+ " DAY (LAST_DAY(CURDATE())) AS last   ) y   WHERE   x.ids <= y.last ) a " + " LEFT JOIN  "
						+ " (SELECT COUNT(1) as customerOrders,c.orderTime   FROM cx_order c where 1=1 and DATE_FORMAT(c.ordertime,'%y-%m') =DATE_FORMAT(CURDATE(),'%y-%m') ");
		if (customerId != null) {
			sql.append(" and c.customerId = '" + customerId + "'");
		}

		sql.append(" GROUP BY DATE_FORMAT(c.orderTime,'%y-%m-%d'))  "
				+ " b ON DATE_FORMAT(a.d,'%y-%m-%d') =DATE_FORMAT(b.orderTime,'%y-%m-%d') ");

		return Db.findFirst(sql.toString());
	}
	
//	/**
//	 * 查询客户当前年每天的成功订单数
//	 */
//	public List<Record> queryYearDayOrders(String customerId) {
//		String sql = "SELECT DATE_FORMAT(c1.orderTime,'%c') as nowMonth,GROUP_CONCAT(c1.customerOrders ORDER BY orderTime ASC) as monthDetail FROM "
//				+ " (SELECT COUNT(1) as customerOrders,c.orderTime FROM cx_order c where 1=1 and YEAR(c.orderTime) =YEAR(NOW()) ";
//		if (customerId != null) {
//			sql += " and c.customerId = '" + customerId + "'";
//		}
//		sql += " GROUP BY DATE_FORMAT(c.orderTime,'%y-%m-%d') "
//				+ " ) c1 where 1=1 and YEAR(c1.orderTime) =YEAR(NOW()) GROUP BY DATE_FORMAT(c1.orderTime,'%y-%m')";
//		return Db.find(sql);
//	}
//	
	
	/**
	 * 首页
	 * 查询每月订单按小时分组，取订单金额最高的前10个小时
	 */
	
	public List<Record> queryHighAmount10Hour(String customerId) {
		String slq = "select DATE_FORMAT(c.orderTime,'%H') AS name,SUM(c.sellPrice) AS value from cx_order c "
				+ " where 1=1 AND c.`status`  = '7'";
		if (customerId != null) {
			slq += " and c.customerId = '" + customerId + "'";
		}

		slq += " AND DATE_FORMAT(c.orderTime,'%y-%m') = DATE_FORMAT(NOW(),'%y-%m')  GROUP BY DATE_FORMAT(c.orderTime,'%Y-%m %H') ";
		return Db.find(slq);

	}
	/**
	 * 首页
	 * 查询客户年度，月度充值金额
	 */
	
	public Record rechargeAmountByTime(String customerId) {
		String rechargeSql = "SELECT car.customerId , " + " (SELECT SUM(car1.amount) from cx_account_record car1 "
				+ "where 1=1 and car1.`status` = '0' and car1.type = '0' AND car1.customerId = car.customerId "
				+ "AND YEAR(car1.createTime) = YEAR(NOW())) as yearRecharge, "

				+ " (SELECT SUM(car2.amount) from cx_account_record car2 "
				+ "where 1=1 and car2.`status` = '0' and car2.type = '0' AND car2.customerId = car.customerId "
				+ "AND DATE_FORMAT(car2.createTime, '%Y%m' ) = DATE_FORMAT(CURDATE() , '%Y%m')) as monthRecharge "
				+ " FROM cx_account_record car where 1=1 and car.customerId = ? GROUP BY car.customerId ";

		return Db.findFirst(rechargeSql, customerId);

	}
	/**
	 * 首页
	 *查询年度以及月度消费金额， 
	 */
	public Record spendingAmountByTime(String customerId) {
		String sql = "SELECT ( "
				+ " SELECT SUM(co1.sellPrice) from cx_order co1 where 1=1 and co1.`status` = '7' AND YEAR(co1.ordertime) = YEAR(NOW()) AND co1.customerId = co.customerId) as yearSpending , "
				+ " (SELECT SUM(co2.sellPrice) from cx_order co2 where 1=1 and co2.`status` = '7'"
				+ " AND DATE_FORMAT(co2.ordertime, '%Y%m' ) = DATE_FORMAT(CURDATE() , '%Y%m') AND co2.customerId =co.customerId ) as monthSpending "
				+ " FROM cx_order co where 1=1 AND co.customerId = ? GROUP BY co.customerId ";
		return Db.findFirst(sql, customerId);

	}
	
	
	/**
	 * 客户复选框
	 * 
	 * @return
	 */
	public String selectCustomer(String selected) {

		List<Customer> list = Customer.dao.find(
				" select customerId,customerName from cx_customer_info where 1=1 and delFlag = '0' order by createTime desc ");

		StringBuffer sb = new StringBuffer("");

		if (list != null && list.size() > 0) {
			for (Customer providerinfo : list) {
				sb.append("<option value=\"");
				sb.append(providerinfo.getStr("customerId"));
				sb.append("\" ");
				sb.append((selected != null && providerinfo.getStr("customerId").equals(selected)) ? "selected" : "");
				sb.append(">");
				sb.append(providerinfo.getStr("customerName"));
				sb.append("</option>");
			}
		}
		return sb.toString();
	}

	public void updateIps(String ids, String ips) {
		Db.update("update cx_customer_info t set t.bindIps=? where t.customerId=?", ips, ids);
	}
}
